package es.udc.acs.banco.operations;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import es.udc.acs.banco.exceptions.SesionInexistenteException;
import es.udc.acs.database.Database;
import es.udc.acs.database.exception.ConexionInvalidaException;

public class DBInicializarBancoOperations {

	private DBInicializarBancoOperations() {}
	
	public static void inicializarBancoDB(String idBanco) 
			                              throws SQLException, 
			                                     SesionInexistenteException, 
			                                     ConexionInvalidaException {
		
		boolean commited = false;
		
		Connection connection = Database.getInstancia().getConexion();

		try {			
			connection.setAutoCommit(false);
			
			borrarBancoDB(idBanco,connection);
			crearBancoDB(idBanco,connection);
			
			connection.commit();
			commited = true;
			connection.setAutoCommit(true);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if (!commited) 
				connection.rollback();
		}
	}
	
	private static void crearBancoDB(String idBanco,
			                         Connection conexion) 
			                         throws SQLException {
		
		PreparedStatement ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,0,0)");		
		ps.setString(1, idBanco);
		ps.execute();		
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,1,100000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,2,200000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,3,300000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,4,400000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,5,500000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,6,600000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,7,700000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,8,800000)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO cbanco VALUES (?,9,900000)");
		ps.setString(1, idBanco);
		ps.execute();
		
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,1,1,1)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,1,2,2)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,1,3,3)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,2,1,1)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,2,2,3)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,2,3,null)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,3,1,4)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,3,2,5)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,3,3,6)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,4,1,7)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,4,2,8)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,4,3,null)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,5,1,0)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,5,2,null)");
		ps.setString(1, idBanco);
		ps.execute();
		ps = conexion.prepareStatement(
				"INSERT INTO ctarjeta VALUES (?,5,3,9)");
		ps.setString(1, idBanco);
		ps.execute();		
	}
	
	
	private static void borrarBancoDB(String idBanco,
									  Connection conexion) 
							          throws SQLException, 
								             SesionInexistenteException, 
								             ConexionInvalidaException {					
		
		if (conexion!=null) {	
			
			DBSesionBancoOperations.borrarSesionBanco(idBanco);
			borrarLogsBanco(idBanco,conexion);
			borrarMovimientosBanco(idBanco,conexion);
			borrarTarjetasBanco(idBanco,conexion);
			borrarCuentasBanco(idBanco,conexion);	
			
		} else {	    
			throw new ConexionInvalidaException();
		}
		
	}
	
	
	private static void borrarMovimientosBanco(String idBanco,
			                                   Connection conexion) 
										       throws SQLException {						

		
		PreparedStatement ps = conexion.prepareStatement(
				"DELETE FROM movimiento WHERE id_banco = ?");
		
		ps.setString(1, idBanco);
		ps.execute();
	}		
	
	
	private static void borrarTarjetasBanco(String idBanco,
                                            Connection conexion)  
											throws SQLException {
									
			
		PreparedStatement ps = conexion.prepareStatement(
				"DELETE FROM ctarjeta WHERE id_banco = ?");
		
		ps.setString(1, idBanco);
		ps.execute();
	}	
	
	
	private static void borrarCuentasBanco(String idBanco,
                                           Connection conexion)  
								           throws SQLException {
						
		
		PreparedStatement ps = conexion.prepareStatement(
				"DELETE FROM cbanco WHERE id_banco = ?");
		
		ps.setString(1, idBanco);
		ps.execute();			
	}	
	
	private static void borrarLogsBanco(String idBanco,
								        Connection conexion)  
									    throws SQLException {


		PreparedStatement ps = conexion.prepareStatement(
		"DELETE FROM b_log WHERE id_banco = ?");
		
		ps.setString(1, idBanco);
		ps.execute();			
	}	
}
